-- retrieving subtypes from the
--Oracle StudentType2,UndergraduateType2,FreshmanType2 type hierarchy
--with corresponding object table Student2 containing all subtypes

-- find all students, regardless of type
SELECT VALUE(s) 
FROM Student2 s;

-- find students who are not undergraduate or freshman types
SELECT VALUE(s)
FROM Student2 s
WHERE value(s) IS OF (ONLY StudentType2) ;

-- find only undergraduates, including freshman type --
SELECT VALUE(s)
FROM Student2 s
WHERE VALUE(s) IS OF (UndergraduateType2);

-- find only freshman type
SELECT VALUE(s)
FROM Student2 s
WHERE VALUE(s) IS OF (ONLY FreshmanType2);

-- the following produces an error message because StudentType2 does not have peermentor--
SELECT lastName, firstName, s.peerMentor
FROM Student2 s
WHERE VALUE(s) IS OF (FreshmanType2);

-- to correct the error, must use TREAT to access peerMentor attribute--
SELECT lastName, firstName, TREAT(VALUE(s) AS FreshmanType2).peerMentor
FROM Student2 s
WHERE VALUE(s) IS OF (FreshmanType2);
